﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using KaoWuYouLibs.Model;
using KaoWuYouLibs.Common;

namespace KaoWuYouLibs.Dal
{
    public class QuestionsAccess
    {
        public int AddQuestion(string Stem, string Answer, int SubjectID, int SubclassID, string Genre)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"INSERT INTO Questions(Stem,AnswerKey,SubjectID,SubclassID,Genre) 
                VALUES (@Stem,@AnswerKey,@SubjectID,@SubclassID,@Genre) SELECT @@IDENTITY";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@Stem", Stem),
                new SqlParameter("@AnswerKey", Answer),
                new SqlParameter("@SubjectID", SubjectID),
                new SqlParameter("@SubclassID", SubclassID),
                new SqlParameter("@Genre", Genre)
            });

                return Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch(Exception ex)
            {
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool DeleteQuestion(int QuestionID)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM Questions WHERE QuestionID = @QuestionID";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@QuestionID", QuestionID) 
            });
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<QuestionModel> FindOwnedQuestions(int TestPaperID)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<QuestionModel> re = new List<QuestionModel>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"SELECT * FROM [Test-Question],Questions 
                WHERE (TestPaperID = @TestPaperID) AND ([Test-Question].QuestionID = Questions.QuestionID)";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@TestPaperID", TestPaperID) 
            });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"],
                        Mark = Convert.ToInt32(sdr["Score"])
                    };
                    re.Add(tmp);
                }
                sdr.Close();
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally 
            {
                conn.Close();
            }
        }

        public List<QuestionModel> FindAllQuestions()
        {

            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<QuestionModel> re = new List<QuestionModel>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Questions";
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"]
                    };
                    re.Add(tmp);
                }
                sdr.Close();
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<QuestionModel> FindQuestionsByGenre(string Genre)
        {

            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<QuestionModel> re = new List<QuestionModel>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Questions WHERE Genre = @Genre";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@Genre", Genre) 
            });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"]
                    };
                    re.Add(tmp);
                }

                sdr.Close();
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<QuestionModel> FindQuestionsByGenreAndSubject(string Genre, int SubjectID)
        {

            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<QuestionModel> re = new List<QuestionModel>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Questions WHERE (Genre = @Genre) AND (SubjectID = @SubjectID)";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@Genre", Genre) ,
                new SqlParameter("@SubjectID",SubjectID)
            });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"]
                    };
                    re.Add(tmp);
                }

                sdr.Close();
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<QuestionModel> FindQuestionsByGenreAndSubjectAndSubclass(string Genre, int SubjectID, int SubclassID)
        {

            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<QuestionModel> re = new List<QuestionModel>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Questions WHERE (Genre = @Genre) AND (SubjectID = @SubjectID) AND (SubClassID = @SubClassID)";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@Genre", Genre) ,
                new SqlParameter("@SubjectID",SubjectID),
                new SqlParameter("@SubClassID",SubclassID)
            });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"]
                    };
                    re.Add(tmp);
                }

                sdr.Close();
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<QuestionModel> FindQuestionsBySubject(int SubjectID)
        {

            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<QuestionModel> re = new List<QuestionModel>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Questions WHERE SubjectID = @SubjectID";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@SubjectID", SubjectID) 
            });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"]
                    };
                    re.Add(tmp);
                }
                sdr.Close();

                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<QuestionModel> FindQuestionsBySubjectAndSubclass(int SubjectID, int SubclassID)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<QuestionModel> re = new List<QuestionModel>();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Questions WHERE (SubClassID = @SubClassID) AND (SubjectID = @SubjectID)";
                cmd.Parameters.AddRange(new SqlParameter[] 
                { 
                    new SqlParameter("@SubClassID", SubclassID) ,
                    new SqlParameter("@SubjectID",SubjectID)
                });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"]
                    };
                    re.Add(tmp);
                }

                sdr.Close();
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
